package Dao;

import Util.DruidUtils;
import bean.Banji;
import bean.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
*
* 班级方法的实现类
*
* */
public class BanjiDaoImpl implements BanjiDao {

    //显示班级
    @Override
    public List<Banji> Banjishow() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =null;
        List<Banji> list = null;
        try {
             connection = DruidUtils.getConnection ();
            String sql = "select * from t_banji";
            preparedStatement = connection.prepareStatement (sql);
            resultSet = preparedStatement.executeQuery ();
            list = new ArrayList<> ();
            while (resultSet.next ()){
                Banji banji = new Banji (resultSet.getInt ("id"),resultSet.getString ("bName"),resultSet.getString ("bAge"),
                        resultSet.getString ("tName"),resultSet.getString ("kouHao"),resultSet.getString ("num"));
                list.add (banji);
            }
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            DruidUtils.close (connection,preparedStatement,resultSet);
        }
        return list;
    }

    //新增班级
    @Override
    public int BanjiAdd(Banji banji) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取连接
            connection = DruidUtils.getConnection ();
            //2.编写sql
            String sql = "insert into t_banji values (null,?,?,?,?,?);";
            //3.执行sql，返回执行的行数
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1,banji.getbName ());
            preparedStatement.setString (2,banji.getbAge ());
            preparedStatement.setString (3,banji.gettName ());
            preparedStatement.setString (4,banji.getKouHao ());
            preparedStatement.setString (5,banji.getNum ());
            return preparedStatement.executeUpdate ();
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            //4.释放资源
            DruidUtils.close (connection,preparedStatement);
        }
        return 0;
    }

    //修改班级
    @Override
    public int BanjiModify(Banji banji) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //获取连接
            connection = DruidUtils.getConnection ();
            String sql = "update t_banji set bName=?,bAge=?,tName=?,kouHao=?,num=? where id =?";

            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1,banji.getbName ());
            preparedStatement.setString (2,banji.getbAge ());
            preparedStatement.setString (3,banji.gettName ());
            preparedStatement.setString (4,banji.getKouHao ());
            preparedStatement.setString (5,banji.getNum ());
            preparedStatement.setInt (6,banji.getId ());
            //返回受影响的行数
            return preparedStatement.executeUpdate ();
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            //释放资源
            DruidUtils.close (connection,preparedStatement);
        }
        return 0;
    }

    //删除资源
    @Override
    public int BanjiDelete(String bName) {

        Connection connection =null;
        PreparedStatement preparedStatement = null;
        //获取连接
        connection = DruidUtils.getConnection ();
        //查询班级中是否还有学生，有就提示班级中还存在学生，删除失败，否则直接删除
        //System.out.println ("dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"+bName);
        if (BanjiFind (bName).size () == 0) {
            //System.out.println ("eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
            String sql = "delete from t_banji where bName = ?";
            try {
                preparedStatement = connection.prepareStatement (sql);
                preparedStatement.setString (1,bName);
                int i = preparedStatement.executeUpdate ();
                return i;
            } catch (SQLException e) {
                e.printStackTrace ();
            } finally {
                DruidUtils.close (connection,preparedStatement);
            }
        }
       return 0;
    }

    //查询班级
    @Override
    public List<Student> BanjiFind(String bName) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Student> list = null;
        try {
            connection = DruidUtils.getConnection ();
            String sql = "select * from t_student s inner join t_banji b on s.sName = b.bName where b.bName = ?";
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1,bName);
            resultSet = preparedStatement.executeQuery ();
            list= new ArrayList<> ();
            while (resultSet.next ()) {
                Student student = new Student (resultSet.getInt ("id"), resultSet.getString ("name"),
                        resultSet.getString ("sex"), resultSet.getString ("birthday"), resultSet.getString ("email"),
                        resultSet.getString ("comments"), resultSet.getString ("sName"));
                list.add (student);
            }
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            DruidUtils.close (connection,preparedStatement,resultSet);
        }
        return list;
    }

}
